package com.kx.center.service.impl;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Properties;

import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Service;

import com.kx.center.service.TradeOnlineService;
import com.kx.center.util.IOHelper;
import com.kx.center.util.SQLHelper;

@Service
public class TradeOnlineServiceImpl implements TradeOnlineService {
	private Properties pp = null;
	private InputStream is = null;
	private String tmp_dir ;
	@Override
	public void addTrade() {
		pp = new Properties();
		String path = TradeOnlineServiceImpl.class.getClassLoader().getResource("application.properties").getPath();
		try {
			pp.load(new FileInputStream(path));
			tmp_dir = pp.getProperty("tmp_dir");
		} catch (Exception e1) {
			e1.printStackTrace();
		}
		// 从本地库中查询未完成或已取消的订单
		// 查询未完成订单 写到文件中
		String sql = "SELECT outer_id FROM  trade WHERE order_state != 0 AND order_state != 40 AND channel_id = 1";
		ArrayList array = SQLHelper.executeQuery(sql, null, SQLHelper.odb);
		ArrayList<String> stringList = new ArrayList<>();
		if (array.size() > 0) {
			for (int i = 0; i < array.size(); i++) {
				Object objs[] = (Object[]) array.get(i);
				stringList.add(objs[0] == null ? " " : objs[0].toString());
			}
			try {
				 IOHelper.delete(tmp_dir+"orderID.txt");
				 IOHelper.writeFile(tmp_dir+"orderID.txt", stringList, "UTF-8", false);

//				IOHelper.delete("C:/orderID.txt");
//				IOHelper.writeFile("C:/orderID.txt", stringList, "UTF-8", false);
			} catch (IOException e) {
				e.printStackTrace();
			}
			// 创建临时表
			String sql1 = "CREATE TABLE  IF NOT EXISTS order_id_tmp(outer_id INT) ";
			SQLHelper.executeUpdate(sql1, null, SQLHelper.kxs);
			// 清空表
			String sql2 = "delete from order_id_tmp";
			SQLHelper.executeUpdate(sql2, null, SQLHelper.kxs);
			// 读取文件，写到数据库临时表中
			// String sql3 = "LOAD DATA LOCAL INFILE '/data/tmp/orderID.txt'
			// INTO
			// TABLE order_id_tmp ";
			String sql3 = "LOAD DATA LOCAL INFILE '"+tmp_dir+"orderID.txt' INTO TABLE order_id_tmp ";
			SQLHelper.executeUpdate(sql3, null, SQLHelper.kxs);
			// 左连接查询符合order_id的订单详情
			String sql4 = "SELECT zx_order.order_id,order_sn,store_id,store_name,buyer_id,add_time,payment_time,finnshed_time,goods_amount,order_amount,shipping_fee,order_state,refund_state,lock_state,delete_state,refund_amount,delay_time,order_from,apply_refund,apply_refund_time,refund_handler_state,'1' FROM zx_order JOIN order_id_tmp  ON zx_order.order_id = order_id_tmp.order_id";
			ArrayList resultList = SQLHelper.executeQuery(sql4, null, SQLHelper.kxs);
			ArrayList<String> arryList = new ArrayList<>();
			for (int i = 0; i < resultList.size(); i++) {
				Object[] objs = (Object[]) resultList.get(i);
				ArrayList<String> strList = new ArrayList<>();
				strList.add(null);
				for (int j = 0; j < objs.length; j++) {
					strList.add(objs[j] == null ? " " : objs[j].toString());
				}
				String Str = StringUtils.join(strList.iterator(), ",");
				arryList.add(Str);
			}
			// 写入临时文件中
			 IOHelper.delete(tmp_dir+"order_tmp.txt");
			 try {
			 IOHelper.writeFile(tmp_dir+"order_tmp.txt", arryList, "UTF-8", false);
			 } catch (IOException e) {
			 e.printStackTrace();
			 }
			
//			IOHelper.delete("C:/order_tmp.txt");
//			try {
//				IOHelper.writeFile("C:/order_tmp.txt", arryList, "UTF-8", false);
//			} catch (IOException e) {
//				e.printStackTrace();
//			}
			String sql41 = "delete from order_id_tmp";
			SQLHelper.executeUpdate(sql41, null, SQLHelper.kxs);
			// 到本数据库中更新
			// 创建临时表
			String sql5 = "create table if not exists trade_tmp like trade";
			SQLHelper.executeUpdate(sql5, null, SQLHelper.odb);
			// 清空表
			String sql6 = "delete from trade_tmp";
			SQLHelper.executeUpdate(sql6, null, SQLHelper.odb);
			
			// load文件数据到临时表中
			// String sql7 = "LOAD DATA LOCAL INFILE '/data/tmp/order_tmp.txt'
			// INTO
			// TABLE order_id_tmp FIELDS TERMINATED BY ','";
			String sql7 = "LOAD DATA LOCAL INFILE '"+tmp_dir+"order_tmp.txt' INTO TABLE trade_tmp FIELDS TERMINATED BY ',' ";
			SQLHelper.executeUpdate(sql7, null, SQLHelper.odb);
			// 更新trade表
			String sql8 = "UPDATE  trade t JOIN trade_tmp t_t ON t.outer_id = t_t.outer_id SET t.add_time = t_t.add_time,"
					+ "t.payment_time = t_t.payment_time," + "t.finnshed_time = t_t.finnshed_time,"
					+ "t.goods_amount = t_t.goods_amount," + "t.order_amount = t_t.order_amount,"
					+ "t.shipping_fee = t_t.shipping_fee," + "t.order_state = t_t.order_state,"
					+ "t.refund_state = t_t.refund_state," + "t.lock_state = t_t.lock_state,"
					+ "t.delete_state = t_t.delete_state," + "t.refund_amount = t_t.refund_amount,"
					+ "t.delay_time = t_t.delay_time," + "t.order_from = t_t.order_from,"
					+ "t.apply_refund = t_t.apply_refund," + "t.refund_handler_state = t_t.refund_handler_state"
					+ " where t.channel_id = 1";
			SQLHelper.executeUpdate(sql8, null, SQLHelper.odb);
			// 删除临时表
			String sql9 = "delete from trade_tmp";
			SQLHelper.executeUpdate(sql9, null, SQLHelper.odb);

		}

		// 插入今日订单数据
		String sql10 = "SELECT order_id,order_sn,store_id,store_name,buyer_id,add_time ,payment_time ,finnshed_time ,goods_amount , "
				+ "order_amount ,shipping_fee ,order_state ,refund_state ,lock_state ,delete_state ,refund_amount ,delay_time , "
				+ "order_from ,apply_refund ,apply_refund_time,refund_handler_state ,'1' channel_id "
				+ "FROM zx_order  WHERE TO_DAYS(FROM_UNIXTIME(add_time)) = TO_DAYS(NOW()) "
				+ "AND  (order_state >=20 OR refund_state >0) ";
		ArrayList orderResults = SQLHelper.executeQuery(sql10, null, SQLHelper.kxs);
		ArrayList<String> strList = new ArrayList<>();
		for (int i = 0; i < orderResults.size(); i++) {
			Object[] objs = (Object[]) orderResults.get(i);
			ArrayList<String> strList2 = new ArrayList<>();
			strList2.add(null);
			for (int j = 0; j < objs.length; j++) {
				strList2.add(objs[j] == null ? " " : objs[j].toString());
			}
			String Str = StringUtils.join(strList2.iterator(), ",");
			strList.add(Str);
		}
		// 写入临时文件中
		 IOHelper.delete(tmp_dir+"new_order_tmp.txt");
		 try {
			 IOHelper.writeFile(tmp_dir+"new_order_tmp.txt", strList, "UTF-8", false);
		 } catch (IOException e) {
		 e.printStackTrace();
		 }
		
//		IOHelper.delete("C:/new_order_tmp.txt");
//		try {
//			IOHelper.writeFile("C:/new_order_tmp.txt", strList, "UTF-8", false);
//		} catch (IOException e) {
//			e.printStackTrace();
//		}
		// 读取文件，写到临时表中
		String sql11 = "CREATE TABLE  IF NOT EXISTS new_order_tmp(trade_id int,outer_id varchar(40),order_sn varchar(30),store_id int ,"
				+ "store_name varchar(50),buyer_id int,add_time int,payment_time int, finnshed_time int,goods_amount decimal,"
				+ " order_amount decimal,shipping_fee decimal,order_state tinyint,refund_state tinyint,lock_state tinyint,"
				+ "delete_state tinyint,refund_amount decimal,delay_time int,order_from tinyint,apply_refund int,apply_refund_time datetime,"
				+ "refund_handler_state int,channel_id int,pay_mode int,dis_count int ,dis_amount int ,odd_amount int) ";
		SQLHelper.executeUpdate(sql11, null, SQLHelper.odb);
		// 清空表
		String sql12 = "delete from new_order_tmp";
		// String sql13 = "LOAD DATA LOCAL INFILE '/data/tmp/new_order_tmp.txt'
		// INTO TABLE order_id_tmp FIELDS TERMINATED BY ','";
		String sql13 = "LOAD DATA LOCAL INFILE '"+tmp_dir+"new_order_tmp.txt' INTO TABLE new_order_tmp FIELDS TERMINATED BY ',' ";
		SQLHelper.executeUpdate(sql13, null, SQLHelper.odb);
		// 插入订单
		String sql133 = "INSERT INTO trade(outer_id,order_sn,store_id,store_name,buyer_id,add_time,payment_time,finnshed_time,goods_amount,order_amount,shipping_fee,order_state,refund_state,lock_state,delete_state,refund_amount,delay_time,order_from,apply_refund,apply_refund_time,refund_handler_state,channel_id)"
				+ " SELECT outer_id,order_sn,store_id,store_name,buyer_id,add_time,payment_time,finnshed_time,goods_amount,order_amount,shipping_fee,order_state,refund_state,lock_state,delete_state,refund_amount,delay_time,order_from,apply_refund,apply_refund_time,refund_handler_state,channel_id FROM new_order_tmp ";
		SQLHelper.executeUpdate(sql133, null, SQLHelper.odb);

		// 读取今日订单的商品，插入到临时表中
		String sql14 = "SELECT rec_id,0,z_o.order_id,goods_id,goods_name,goods_pay_price,goods_num,goods_pay_price,z_o.store_id,z_o.buyer_id,"
				+ "goods_type,promotions_id,gc_id,seckill_id,seckill_status,'1' channel_id ,bl_sku,goods_sku FROM zx_order z_o "
				+ " JOIN zx_order_goods z_o_g ON z_o.order_id = z_o_g.order_id "
				+ " WHERE TO_DAYS(FROM_UNIXTIME(add_time)) = TO_DAYS(NOW())AND  (order_state >=20 OR refund_state >0)";
		ArrayList orderResults2 = SQLHelper.executeQuery(sql14, null, SQLHelper.kxs);
		ArrayList<String> strList2 = new ArrayList<>();
		for (int i = 0; i < orderResults2.size(); i++) {
			Object[] objs = (Object[]) orderResults2.get(i);
			ArrayList<String> strList3 = new ArrayList<>();
			for (int j = 0; j < objs.length; j++) {
				strList3.add(objs[j] == null ? " " : objs[j].toString());
			}
			String Str = StringUtils.join(strList3.iterator(), ",");
			strList2.add(Str);
		}
		// 写入临时文件中
		 IOHelper.delete(tmp_dir+"new_goods_tmp.txt");
		 try {
			 IOHelper.writeFile(tmp_dir+"new_goods_tmp.txt", strList2, "UTF-8", false);
		 } catch (IOException e) {
		 e.printStackTrace();
		 }
		
//		IOHelper.delete("C:/new_goods_tmp.txt");
//		try {
//			IOHelper.writeFile("C:/new_goods_tmp.txt", strList2, "UTF-8", false);
//		} catch (IOException e) {
//			e.printStackTrace();
//		}
		// 读取文件，写到临时表中
		String sql15 = "CREATE TABLE  IF NOT EXISTS new_goods_tmp like trade_detail ";
		SQLHelper.executeUpdate(sql15, null, SQLHelper.odb);
		// 清空表
		String sql16 = "delete from new_goods_tmp";
		SQLHelper.executeUpdate(sql16, null, SQLHelper.odb);
		// String sql17 = "LOAD DATA LOCAL INFILE '/data/tmp/new_goods_tmp.txt'
		// INTO TABLE new_goods_tmp FIELDS TERMINATED BY ','";
		String sql17 = "LOAD DATA LOCAL INFILE '"+tmp_dir+"new_goods_tmp.txt' INTO TABLE new_goods_tmp FIELDS TERMINATED BY ',' ";
		SQLHelper.executeUpdate(sql17, null, SQLHelper.odb);
		// 执行更新goods临时表goods_id
		String sql18 = "UPDATE new_goods_tmp n_g_t  JOIN goods g ON g.sku = n_g_t.goods_sku  SET n_g_t.goods_id = g.id ";
		SQLHelper.executeUpdate(sql18, null, SQLHelper.odb);
		//更新promotionId 
		String sql181 = "UPDATE trade_detail t_d JOIN bundle b ON t_d.bundle_sku = b.sku SET t_d.promotions_id = b.id WHERE channel_id = 1";
		SQLHelper.executeUpdate(sql181, null, SQLHelper.odb);
		// 将临时表中goods插入到trade_detail表中
		String sql19 = "INSERT INTO trade_detail SELECT * FROM new_goods_tmp";
		SQLHelper.executeUpdate(sql19, null, SQLHelper.odb);
		//更新trade_detail的trade_id
		String sql20 = "UPDATE trade_detail t_d JOIN trade t_t ON t_d.outer_id = t_t.outer_id SET t_d.trade_id = t_t.trade_id WHERE t_d.channel_id = 1";
		SQLHelper.executeUpdate(sql20, null, SQLHelper.odb);
		// 清空表
		String sql21 = "delete from new_goods_tmp";
		SQLHelper.executeUpdate(sql21, null, SQLHelper.odb);
		// 清空订单临时表
		String sql134 = "DELETE FROM new_order_tmp";
		SQLHelper.executeUpdate(sql134, null, SQLHelper.odb);
		
		//更新库存
		String sql22 = "CREATE TABLE IF NOT EXISTS store_goods_temp LIKE store_goods ";
		SQLHelper.executeUpdate(sql22, null, SQLHelper.odb);
		//清空临时表
		String sql23 = "delete from store_goods_temp";
		SQLHelper.executeUpdate(sql23, null, SQLHelper.odb);
		//查询各门店销售额及商品id到临时表中
		String sql24 = "INSERT INTO store_goods_temp(goods_id,store_id,storage) SELECT t_d.goods_id,t.store_id, SUM(t_d.goods_num) selled_quantity "
				+ " FROM trade t JOIN trade_detail t_d ON t_d.outer_id = t.outer_id WHERE TO_DAYS(FROM_UNIXTIME(t.add_time)) = TO_DAYS(NOW()) AND t.channel_id = 1  GROUP BY goods_id,t.store_id ";
		SQLHelper.executeUpdate(sql24, null, SQLHelper.odb);
		
		//执行更新
		String sql25 = "UPDATE store_goods s_g JOIN store_goods_temp s_g_t ON s_g.goods_id = s_g_t.goods_id AND s_g.store_id = s_g_t.store_id "
				+ "SET s_g.ref_storage = (s_g.storage - s_g_t.storage)";
		SQLHelper.executeUpdate(sql25, null, SQLHelper.odb);
		//清空临时表
		SQLHelper.executeUpdate(sql23, null, SQLHelper.odb);
		
		//判断数据库connect情况
	}

	@Override
	public void readFile2Order() {
		// TODO Auto-generated method stub

	}

	@Override
	public void writeOrder2File() {

	}

}
